package com.gitee.cliveyuan.tools.data;

import com.gitee.cliveyuan.tools.Assert;
import com.gitee.cliveyuan.tools.CollectionTools;
import com.gitee.cliveyuan.tools.FileTools;
import com.gitee.cliveyuan.tools.StringTools;
import com.gitee.cliveyuan.tools.exception.ExcelException;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

import static com.gitee.cliveyuan.tools.enums.ExcelType.XLS;
import static com.gitee.cliveyuan.tools.enums.ExcelType.XLSM;
import static com.gitee.cliveyuan.tools.enums.ExcelType.XLSX;

/**
 * @author clive
 * Created on 2018/08/01
 * @since 1.0
 */
public class ExcelTools {

    private static final Logger logger = LoggerFactory.getLogger(ExcelTools.class);

    private ExcelTools(){}


    /**
     * 读入excel文件，解析后返回
     * key: sheet名, value每行数据
     *
     * @param absoluteFilePath 文件绝对路径
     * @param sheetNames 指定的sheet名
     */
    public static Map<String, List<String[]>> readExcel(String absoluteFilePath, String... sheetNames) {
        Assert.notBlank(absoluteFilePath, "excel file path is empty");
        File file = new File(absoluteFilePath);
        Assert.isTrue(file.exists(), "excel file not exists");
        String extension = FileTools.getExtension(absoluteFilePath);
        if (!XLS.name().equalsIgnoreCase(extension)
                && !XLSX.name().equalsIgnoreCase(extension)
                && !XLSM.name().equalsIgnoreCase(extension)
                ) {
            throw ExcelException.notSupport(extension);
        }
        Map<String, List<String[]>> map = Maps.newHashMap();
        try {
            // 检查文件
            // 获得Workbook工作薄对象
            Workbook workbook = getWorkBook(file);
            if (Objects.isNull(workbook)) throw ExcelException.excelReadError();
            // 仅读取第一个sheet, 如需读取其他sheet, 需自行封装方法 获取sheet数量:workbook.getNumberOfSheets()
            // 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
            int numberOfSheets = workbook.getNumberOfSheets();
            List<Sheet> sheets = Lists.newArrayList();
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (Objects.isNull(sheet)) {
                    throw ExcelException.failToReadSheet();
                }
                sheets.add(sheet);
            }
            if (CollectionTools.isNotEmpty(sheetNames)) {
                List<String> sheetNameList = Arrays.asList(sheetNames);
                List<Sheet> collect = sheets.stream().filter(x -> sheetNameList.contains(x.getSheetName())).collect(Collectors.toList());
                sheets.clear();
                sheets.addAll(collect);
            }
            for (Sheet sheet : sheets) {
                List<String[]> list = Lists.newArrayList();

                // 获得当前sheet的开始行
                int firstRowNum = sheet.getFirstRowNum();
                // 获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
                    // 获得当前行
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    // 获得当前行的开始列
                    int firstCellNum = row.getFirstCellNum();
                    // 获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    String[] cells = new String[row.getPhysicalNumberOfCells()];
                    int blankCol = 0;
                    // 循环当前行
                    for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        String cellStr = getCellValue(cell);
                        if (StringTools.isBlank(cellStr)) {
                            blankCol++;
                        }
                        cells[cellNum] = cellStr;
                    }
                    if (blankCol < lastCellNum) {
                        list.add(cells);
                    }
                }
                map.put(sheet.getSheetName(), list);
            }

        } catch (ExcelException e) {
            throw e;
        } catch (Exception e) {
            logger.error("readExcel ", e);
            throw ExcelException.failToParseExcel();
        }
        return map;
    }

    private static Workbook getWorkBook(File file) throws IOException {
        // 获得文件名
        String fileName = file.getName();
        // 创建Workbook工作薄对象，表示整个excel
        Workbook workbook;
        // 获取excel文件的io流
        InputStream is = new FileInputStream(file);
        // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
        if (fileName.endsWith(XLS.name().toLowerCase())
                || fileName.endsWith(XLS.name())) {
            // 2003
            workbook = new HSSFWorkbook(is);
        } else {
            // 2007
            workbook = new XSSFWorkbook(is);
        }

        return workbook;
    }

    private static String getCellValue(Cell cell) {
        String cellValue = StringTools.EMPTY;
        if (Objects.isNull(cell)) {
            return cellValue;
        }
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                DecimalFormat df = new DecimalFormat("0");
                cellValue = df.format(cell.getNumericCellValue());
                break;
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case _NONE:
                cellValue = "[未知类型]";
                break;
            case ERROR:
                cellValue = "[非法字符]";
                break;
        }
        return cellValue;
    }
}
